PROBLEM: You need to copy the table definition for all tables in a database between two SQL servers.
SOLUTION: One of the requirements is to use PowerShell and I will use it in combination with SQLPSX. Yes, I will use SQLPSX again and you probably have noticed I use it in all my PowerShell scripts that access MS SQL Server –I’m a fan, what else can I say.
There are a few highlighted lines of code I want to mention because I had a bit of trouble while scripting tables with dependencies on user defined data types and partitioned tables. Line 5 and 6 are string variables assigments for the target server and database. On line 13, I added the scripting option “NoFileGroup” because the source database was set up using multiple filegroups and the script failed when these file groups were not available on the target server; the target server in this example will have a single file group. On line 15, I added the “WithDependencies” which helped with the creation of partitioned table functions and schemas. And on line 16, I used the “IncludeIfNotExists” in order to avoid errors if the database objects already exists.
CLEAR
$sSRCServer = "TUIRADEVELOPMENT"
$sSRCDatabase = "AdventureWorks"
$sTRGServer = "TUIRA"
$sTRGDatabase = "AdventureWorks"
$oSO = New-SqlScriptingOptions
$oSO.Permissions = $false
$oSO.ClusteredIndexes = $true
$oSO.Indexes = $true
$oSO.Triggers = $true
$oSO.NoFileGroup = $true
$oSO.NonClusteredIndexes = $true
$oSO.WithDependencies = $true
$oSO.IncludeIfNotExists = $true
$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable
foreach ($oTable in $oTables){
$oT = $oTable.Name
$oS = $oTable.Schema
$DDL = Get-SqlScripter $oTable -scriptingOptions $oSO
invoke-sqlcmd -ServerInstance "$sTRGServer" -Database "$sTRGDatabase" `
-Query "$DDL"
}
This solution can easily be converted to a function with optional parameters that map to scripting options. The target variables for server and/or database could hold arrays for copying the tables definition to multiple servers. A form could also be developed using WPF to make it user friendly. What do you think?
